package com.yuncheng.ccsystem.excel.controller;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.yuncheng.annotation.AutoLog;
import com.yuncheng.ccsystem.excel.dto.ExcelTemplateDTO;
import com.yuncheng.common.utils.ExcelUtil;
import com.yuncheng.utils.I18nUtil;
import com.yuncheng.vo.HttpResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

@Api(tags = "Excel操作接口")
@RestController
@RequestMapping("api/system/excel")
public class ExcelController {

    private static final Logger logger = LoggerFactory.getLogger(ExcelController.class);

    /**
     * 导入Excel获取数据
     * @param request HttpServletRequest
     * @return Excel数据list
     */
    @AutoLog(value = "导入Excel获取数据")
    @ApiOperation(value = "导入Excel获取数据")
    @PostMapping("/importXls")
    public HttpResult<?> importXls(HttpServletRequest request) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        Set<Map.Entry<String, MultipartFile>> fileSet = fileMap.entrySet();
        if (fileSet.size() == 0) {
            return HttpResult.error(I18nUtil.message("excel.import.error"));
        }
        Map.Entry<String, MultipartFile> entryMap = fileSet.iterator().next();
        MultipartFile file = entryMap.getValue();

        // 格式
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        params.setSheetNum(1);

        try {
            // 校验页签名称
            Workbook workBook = HSSFWorkbookFactory.create(file.getInputStream());
            Sheet sheet = workBook.getSheetAt(0);
            if (sheet == null) {
                return HttpResult.error(I18nUtil.message("excel.import.error.sheet"));
            }

            return HttpResult.ok(ExcelImportUtil.importExcel(file.getInputStream(), Map.class, params));
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return HttpResult.error(I18nUtil.message("excel.import.error"));
        } finally {
            try {
                file.getInputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出Excel模板
     * @param excelTemplateDTO ExcelTemplateDTO
     * @param response HttpServletResponse
     */
    @PostMapping("/exportTemplate")
    public void exportTemplate(@RequestBody ExcelTemplateDTO excelTemplateDTO, HttpServletResponse response) {
        List<ExcelExportEntity> entity = new ArrayList<>();
        List<String> colNames = excelTemplateDTO.getColNames();
        Map<String, List<Map<String, String>>> colDict = excelTemplateDTO.getColDict();

        List<Map<String, Object>> resultList_1 = new ArrayList<>();
        for (String colName : colNames) {
            // 要导入的字段
            ExcelExportEntity excelExportEntity = new ExcelExportEntity(colName);
            entity.add(excelExportEntity);
        }

        List<Map<String, Object>> sheetsList = new ArrayList<>();
        sheetsList.add(getSheet(excelTemplateDTO.getTitle(), excelTemplateDTO.getTitle(), resultList_1, entity));

        Workbook workBook = null;
        try {
            workBook = ExcelUtil.exportExcel(sheetsList, ExcelType.HSSF);
            Sheet sheet = workBook.getSheetAt(0);

            // 将所有列都设置为文本类型，避免格式的问题
            CellStyle cellStyle = workBook.createCellStyle();
            cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("TEXT"));
            for (int i = 0; i < sheet.getRow(1).getPhysicalNumberOfCells(); i++) {
                sheet.setDefaultColumnStyle(i, cellStyle);
            }

            // 设置下拉数据有效性
            if(CollUtil.isNotEmpty(colDict)) {
                for (Map.Entry<String, List<Map<String, String>>> entry : colDict.entrySet()) {
                    String colName = entry.getKey();
                    List<Map<String, String>> dict = entry.getValue();
                    int colIndex = colNames.indexOf(colName);
                    List<String> explicitList = dict.stream().map(item -> item.get("value")).collect(Collectors.toList());

                    if(StrUtil.join(",", explicitList).length() > 255) {
                        // 创建隐藏页，用于存dict
                        String dictSheetName = "dictSheet_" + colName;
                        Sheet dictSheet = workBook.createSheet(dictSheetName);
                        for (int i = 0; i < dict.size(); i++) {
                            Row row = dictSheet.createRow(i);
                            Cell cell = row.createCell(0);
                            cell.setCellValue(dict.get(i).get("value"));
                        }
                        //dictSheetName + ! 定位到用来加载列的新的sheet页，后面则是A列的1-65535为有效性List条件
                        String strFormula = dictSheetName + "!$A$1:$A$65535";
                        DataValidationConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);
                        // 设置数据有效性的范围
                        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 65535, colIndex, colIndex);
                        // 数据有效性对象
                        DataValidationHelper help = new HSSFDataValidationHelper((HSSFSheet) sheet);
                        DataValidation validation = help.createValidation(constraint, cellRangeAddressList);
                        sheet.addValidationData(validation);
                        // 隐藏dictSheet
                        workBook.setSheetHidden(workBook.getSheetIndex(dictSheet), true);
                        // TODO: 隐藏sheet，在保存时会有提示，暂未找到其他解决方案。
                    } else {
                        // 方案二：直接设置DataValidation，但constraint不能过长。
                        // 需要进行格式化的范围
                        CellRangeAddressList regions = new CellRangeAddressList(2, 65535, colIndex, colIndex);
                        // 设置下拉列表值
                        DVConstraint constraint = DVConstraint.createExplicitListConstraint(explicitList.toArray(new String[]{}));
                        // 结合范围设置数据有效性
                        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
                        // 为该sheet添加数据有效性
                        sheet.addValidationData(dataValidation);
                    }
                }
            }

            workBook.write(response.getOutputStream());
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            if (workBook != null) {
                try {
                    workBook.close();
                } catch (IOException e) {
                    logger.error(e.getMessage(), e);
                }
            }
        }
    }

    /**
     * 获取Excel的sheet
     * @param title 标题
     * @param sheetName sheet名称
     * @param resultList 指定要导出的List集合数据
     * @param entity 指定ExcelExportEntity
     * @return Map
     */
    private Map<String, Object> getSheet(String title, String sheetName, List<Map<String, Object>> resultList, List<ExcelExportEntity> entity) {
        //创建导出参数
        ExportParams params = new ExportParams();
        params.setTitle(title);
        params.setSheetName(sheetName);
        params.setType(ExcelType.HSSF);
        Map<String, Object> sheetMap = new HashMap<>();
        //指定要导出的List集合数据
        sheetMap.put("data", resultList);
        //指定ExcelExportEntity
        sheetMap.put("entity", entity);
        //指定要导出的参数
        sheetMap.put("title", params);
        return sheetMap;
    }

}
